﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace ProjectShare
{
    public class DataExportExcel
    {
        /// <summary>
        /// 导出报表内容到Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public static string RenderToExcel<T>(List<T> list,string ReportCode)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();
            string filePath = "";
            string TableName = "";//文件名称
            ISheet sheet = workbook.CreateSheet("sheet1");
            IRow headerRow = sheet.CreateRow(0);//表头
            switch (ReportCode)
            {
                //蔬菜基本情况调查表
                //case "VegBasicInfo":
                //    VegBasicInfoServices vegBasicInfoServices = new VegBasicInfoServices();
                //    model.ReportId = reportId;
                //    model.list = vegBasicInfoServices.(reportId);
                //    break;
                ////蔬菜设施情况表
                //case "VegFacility":
                //    VegFacilityServices vegFacilityServices = new VegFacilityServices();
                //    model.ReportId = reportId;
                //    model.list = vegFacilityServices.GetModel(reportId);
                //    break;
                ////蔬菜技术推广机构及人员情况统计表
                //case "VegTecPromotion":
                //    VegTecPromotionServices vegTecPromotionServices = new VegTecPromotionServices();
                //    model.ReportId = reportId;
                //    model.list = vegTecPromotionServices.GetInfo(reportId);
                //    break;
                //蔬菜外销情况调查表
                case "VegExport":
                    TableName = "蔬菜外销情况表"+DateTime.Now.ToString("yyyyMMddhhmmss").Trim();
                    break;
                    ////蔬菜生产经营实体情况表
                    //case "VegProsecution":
                    //    VegProsecutionServices vegProsecutionServices = new VegProsecutionServices();
                    //    model.ReportId = reportId;
                    //    model.list = vegProsecutionServices.GetInfo(reportId);
                    //    break;
                    ////主要蔬菜生产情况调查表
                    //case "VegProSurvey":
                    //    VegProSurveyServices vegProSurveyServices = new VegProSurveyServices();
                    //    model.ReportId = reportId;
                    //    model.list = vegProSurveyServices.GetInfo(reportId);
                    //    break;
            }
            int rowIndex = 1, piIndex = 0;
            Type type = typeof(T);
            PropertyInfo[] pis = type.GetProperties();
            int pisLen = pis.Length; //根据数据项获取列数
            PropertyInfo pi = null;
            headerRow.CreateCell(0).SetCellValue("种类");
            headerRow.CreateCell(1).SetCellValue("详细类别");
            headerRow.CreateCell(2).SetCellValue("单位");
            headerRow.CreateCell(3).SetCellValue("数量");
            foreach (T data in list)
            {
                piIndex = 0;
                IRow dataRow = sheet.CreateRow(rowIndex);
                while (piIndex < pisLen)
                {
                    pi = pis[piIndex];
                    try
                    {
                        dataRow.CreateCell(piIndex).SetCellValue(pi.GetValue(data).ToString());
                    }
                    catch (Exception)
                    {
                        dataRow.CreateCell(piIndex).SetCellValue("");
                    }
                    piIndex++;
                }
                rowIndex++;
            }
            sheet.SetColumnWidth(0, 200);//设置列宽
            sheet.SetColumnWidth(1, 200);//设置列宽
            sheet.SetColumnWidth(2, 100);//设置列宽
            sheet.SetColumnWidth(3, 100);//设置列宽
            workbook.Write(ms);
            filePath = @"D:\\" + TableName + ".xls";//文件存放路径
            FileStream dumpFile = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite);
            ms.WriteTo(dumpFile);
            ms.Flush();
            ms.Position = 0;
            dumpFile.Close();
            return filePath;
        }
        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="lists"></param>
        /// <param name="head">中文列名对照</param>
        /// <param name="workbookFile">Excel所在路径</param>
        /// <returns></returns>
        public List<T> RendToList(Hashtable head, string workbookFile)
        {
            try
            {
                HSSFWorkbook hssfworkbook;
                List<T> lists = new List<T>();
                using (FileStream file = new FileStream(workbookFile, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                HSSFSheet sheet = hssfworkbook.GetSheetAt(0) as HSSFSheet;
                IEnumerator rows = sheet.GetRowEnumerator();
                HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
                int cellCount = headerRow.LastCellNum;
                //Type type = typeof(T);
                PropertyInfo[] properties;
                T t = default(T);
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    HSSFRow row = sheet.GetRow(i) as HSSFRow;
                    t = Activator.CreateInstance<T>();
                    properties = t.GetType().GetProperties();
                    foreach (PropertyInfo column in properties)
                    {
                        int j = headerRow.Cells.FindIndex(delegate (ICell c)
                        {
                            return c.StringCellValue == (head[column.Name] == null ? column.Name : head[column.Name].ToString());
                        });
                        if (j >= 0 && row.GetCell(j) != null)
                        {
                            object value = valueType(column.PropertyType, row.GetCell(j).ToString());
                            column.SetValue(t, value, null);
                        }
                    }
                    lists.Add(t);
                }
                return lists;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("读取Excel中的数据时发生错误，可能Excel数据格式与指定格式不一致。" + ex.Message + "\r\n");
                return null;
            }
        }
        object valueType(Type t, string value)
        {
            object o = null;
            string strt = "String";
            if (t.Name == "Nullable`1")
            {
                strt = t.GetGenericArguments()[0].Name;
            }
            switch (strt)
            {
                case "Decimal":
                    o = decimal.Parse(value);
                    break;
                case "Int":
                    o = int.Parse(value);
                    break;
                case "Float":
                    o = float.Parse(value);
                    break;
                case "DateTime":
                    o = DateTime.Parse(value);
                    break;
                default:
                    o = value;
                    break;
            }
            return o;
        }
    }
}
